# -*- coding: utf-8 -*-
import pymysql
import cx_Oracle
import psycopg2
from pymysql.constants import CLIENT
from clickhouse_driver import Client
from redis import StrictRedis
from tools.Initialization import DATABASE_INFO, APP_DB_NAME, ACCOUNT_DB_INFO, ACCOUNT_DB_NAME, SYS_DB_NAME
from sshtunnel import SSHTunnelForwarder
from pyLib.YamlManage import YamlReader, PAR_CONFIG
from pyLib.LogManage import LOG, decorator_log

par_config_info = YamlReader(file_path=PAR_CONFIG).get_yaml_data()
# 获取连接clickhouse的信息
clickhouse_info = par_config_info.get("clickhouse")
# 获取连接redis的信息
redis_info = par_config_info.get("redis")
# 获取连接oracle的信息
oracle_info = par_config_info.get("oracle")


class MysqlManager:
    """mysql管理器"""

    def __init__(
            self,
            user: str,
            passwd: str,
            host: str,
            port: int,
            db: str = None,
            connect_ssh: list = None,
            charset='utf8'
    ):
        """初始化数据库"""
        self.__db = db
        self.__user = user
        self.__passwd = passwd
        self.__host = host
        self.__port = port
        self.__charset = charset
        self.__connect = None
        # 如果connect_SSH存在
        if connect_ssh:
            # 连接ssh
            self.server = self.get_server(
                host=connect_ssh[3],
                port=connect_ssh[4],
                username=connect_ssh[1],
                password=connect_ssh[2]
            )
        else:
            self.server = None

    def get_server(self, host, port, username, password):
        """连接ssh服务"""
        SSH = {
            "ssh_address_or_host": host,
            "ssh_port": port,
            "ssh_password": password,
            "ssh_username": username
        }
        server = SSHTunnelForwarder(**SSH, remote_bind_address=(self.__host, self.__port))
        return server

    def _connect_db(self):
        """
        连接数据库
        """
        if self.server:
            # 启动ssh服务
            self.server.start()
            # 更改绑定的host和端口号
            self.__host = '127.0.0.1'
            self.__port = self.server.local_bind_port
        params = {
            "db": self.__db,
            "user": self.__user,
            "passwd": self.__passwd,
            "host": self.__host,
            "port": self.__port,
            "charset": self.__charset
        }
        self.__connect = pymysql.connect(**params, client_flag=CLIENT.MULTI_STATEMENTS)
        self.__cursor = self.__connect.cursor()

    def _close_db(self):
        """
        关闭数据库
        """
        self.__cursor.close()
        self.__connect.close()
        # 关闭ssh
        if self.server:
            self.server.stop()

    def switch_db(self, dbname):
        """
        切换数据库
        :param dbname: 数据库名
        """
        self.__db = dbname

    @decorator_log
    def create_database(self, dbname: str, switch_to: bool = False):
        """
        创建数据库
        :param dbname: 数据库名
        :param switch_to: 是否切换到新建的数据库
        """
        self.execute_sql(
            f"create database if not exists {dbname};"
        )
        if switch_to:
            self.__db = dbname

    def delete_database(self, dbname: str):
        """
        删除数据库
        :param dbname: 数据库名
        """
        self.execute_sql(
            f"DROP DATABASE IF EXISTS {dbname};"
        )

    def db_insert(self, table, insert_data):
        """
        插入数据到数据库
        str -> table 为字符串
        [{},{}] -> insert_data 为列表中嵌套字典类型
        """
        # 用户传入数据字典列表数据，根据key, value添加进数据库
        for data in insert_data:
            # 提取插入的字段
            key = ','.join(data.keys())
            # 提取插入的值
            insert_data = ','.join(data.values())
            # 构建sql语句
            sql = "insert into {table} ({key}) values ({val})".format(
                table=table,
                key=key,
                val=insert_data
            )
            self.execute_sql(sql)

    def db_delete(self, table, condition=None):
        """
        :param table: 表名
        :param condition: 删除sql的条件
        """
        sql = f"delete from {table}"
        if condition:
            sql += f" where {condition}"
        self.execute_sql(sql)

    def db_update(self, table, update_data, condition=None):
        """
        更新数据
        :param table: 表名
        :param update_data: 更新的sql数据
        :param condition: 条件表达式
        """
        # 如果有条件
        if condition:
            sql = "update {table} set {values} where {condition}".format(
                table=table,
                values=update_data,
                condition=condition
            )
        else:
            sql = "update {table} set {values}".format(table=table, values=update_data)
        self.execute_sql(sql)

    def get_db_data(self, table, show_list, condition=None):
        """
        查询指定表的信息
        :param table: 表名
        :param show_list: 需要显示的字段，列表类型[]
        :param condition:查询条件
        :param get_one:是否只查询一条数据，默认查询所有
        :return:查询结果
        """
        # 处理显示的数据
        show_list = ",".join(
            [column_name if column_name == "*" or " " in column_name else f"`{column_name}`" for column_name in
             show_list])
        sql = "select {key} from {table}".format(key=show_list, table=table)
        # 处理传入的条件
        if condition:
            sql = "select {key} from {table} where {condition}".format(
                key=show_list,
                table=table,
                condition=condition
            )
        result = self.execute_sql(sql)
        return result

    def execute_sql(self, sql):
        """
        :param sql: 执行的sql
        :return: 查询结果
        """
        self._connect_db()
        if type(sql) is list:
            for i in sql:
                self.__cursor.execute(i)
        elif type(sql) is str:
            self.__cursor.execute(sql)
        result = self.__cursor.fetchall()
        self.__connect.commit()
        self._close_db()
        return result

    def batch_insert(self, table: str, insert_list: list):
        """
        批量插入数据
        :param table: 表名
        :param insert_list: 插入的数据列表 --> [(id,'NAME','age'), ...]
        """
        # 查询数据表字段
        try:
            columns = self.execute_sql(
                f"SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='{self.__db}' AND TABLE_NAME='{table}';"
            )
            column_names = ','.join([f"`{column[0]}`" for column in columns])
            insert_data = [str(values) for values in insert_list]
            sql = f"INSERT INTO `{table}` ({column_names}) VALUES " + ",".join(insert_data)
            # 执行插入语句
            self.execute_sql(sql)
        except Exception as error:
            LOG.error(error)

    def get_table_list(self, table_names=None):
        """
        模糊查询数据库里的表
        :param table_names: 表名
        :return:查询结果
        """
        result = None
        self._connect_db()
        if isinstance(table_names, str):
            sql = f"show tables like '{table_names}';"
            self.__cursor.execute(sql)
            result = self.__cursor.fetchall()
        elif isinstance(table_names, list):
            result = ()
            for table in table_names:
                sql = f"show tables like '{table}';"
                self.__cursor.execute(sql)
                tables = self.__cursor.fetchall()
                result = result + tables
        elif table_names is None:
            sql = f"show tables"
            self.__cursor.execute(sql)
            result = self.__cursor.fetchall()
        self._close_db()
        return result

    def drop_table(self, table):
        """删除指定数据表"""
        if isinstance(table, list):
            for table_name in table:
                sql = f"DROP TABLE IF EXISTS {table_name}"
                self.execute_sql(sql)
        elif isinstance(table, str):
            sql = f"DROP TABLE IF EXISTS {table}"
            self.execute_sql(sql)

    def del_tables_for_app_id(self, app_id):
        """
        根据app id删除相关表
        """
        try:
            result_value = self.get_table_list()
            tb_list_ = []
            for tb in [i[0] for i in result_value]:
                if app_id in tb:
                    tb_list_.append(tb)
            self.drop_table(tb_list_)
        except Exception as e:
            LOG.error("APP相关元素表删除失败：{}".format(str(e)))


class ClickhouseManager:
    """clickhouse管理器"""

    def __init__(
            self,
            db=APP_DB_NAME,
            user=clickhouse_info.get('db_user'),
            passwd=clickhouse_info.get('db_pwd'),
            host=clickhouse_info.get('db_host'),
            port=clickhouse_info.get('db_port'),
            send_receive_timeout=5
    ):
        """初始化数据库"""
        self.__database = db
        self.__user = user
        self.__password = passwd
        self.__host = host
        self.__port = int(port)
        self.__send_receive_timeout = send_receive_timeout
        self.__client = None

    def _connect_db(self):
        """
        连接数据库
        """
        params = {
            "database": self.__database,
            "user": self.__user,
            "password": self.__password,
            "host": self.__host,
            "port": self.__port,
            "send_receive_timeout": self.__send_receive_timeout
        }
        self.__client = Client(**params)

    def execute_sql(self, sql):
        self._connect_db()
        res = self.__client.execute(sql)
        return res

    def create_database(self, dbname: str, switch_to: bool = False):
        """
        创建数据库
        :param dbname: 数据库名
        :param switch_to: 是否切换到新建的数据库
        """
        self.execute_sql(
            f"create database if not exists {dbname};"
        )
        if switch_to:
            self.__database = dbname

    def delete_database(self, dbname: str):
        """
        删除数据库
        :param dbname: 数据库名
        """
        self.execute_sql(
            f"DROP DATABASE IF EXISTS {dbname};"
        )

    def db_delete(self, table, condition="1=1"):
        """
        :param table: 表名
        :param condition: 删除sql的条件
        """
        sql = f"ALTER TABLE {table} DELETE where {condition}"
        self.execute_sql(sql)


class RedisManager:
    """redis管理器"""

    def __init__(
            self,
            host=redis_info.get('db_host'),
            port=int(redis_info.get('db_port')),
            password=redis_info.get('db_pwd')
    ):
        self.redispool = StrictRedis(host=host, port=port, password=password)

    def set(self, key, value):
        """插入内容"""
        self.redispool.set(key, value)

    def get(self, key):
        """获取内容"""
        value = self.redispool.get(key).decode()
        return value


class OracleManager:
    """Oracle管理器"""

    def __init__(
            self,
            user=oracle_info.get('db_user'),
            pwd=oracle_info.get('db_pwd'),
            host=oracle_info.get('db_host'),
            port=oracle_info.get('db_port'),
            sid=oracle_info.get('sid')
    ):
        self.__user = user
        self.__pwd = pwd
        self.__host = host
        self.__port = port
        self.__sid = sid
        self.__connect = None
        self.__cursor = None

    def _connect_db(self):
        """
        连接数据库
        """
        self.__connect = cx_Oracle.connect(
            f"{self.__user}/{self.__pwd}@{self.__host}:{self.__port}/{self.__sid}"
        )
        self.__cursor = self.__connect.cursor()

    def _close_db(self):
        """
        关闭数据库
        """
        self.__cursor.close()
        self.__connect.close()

    def execute_sql(self, sql):
        """
        :param sql: 执行的sql
        :return: 查询结果
        """
        self._connect_db()
        if type(sql) is list:
            for i in sql:
                self.__cursor.execute(i)
        elif type(sql) is str:
            self.__cursor.execute(sql)
        result = self.__cursor.fetchall()
        self.__connect.commit()
        self._close_db()
        return result


class PostGreManager(MysqlManager):

    def __init__(self, database, user, passwd, host, port):
        """初始化数据库"""
        super().__init__(user, passwd, host, port)
        self.__database, self.__schema = database.split(".")
        self.__user = user
        self.__password = passwd
        self.__host = host
        self.__port = port
        self.__client = None

    def _connect_db(self):
        """
        连接数据库
        """
        params = {
            "database": self.__database,
            "user": self.__user,
            "password": self.__password,
            "host": self.__host,
            "port": self.__port
        }
        self.__connect = psycopg2.connect(**params, options=f"-c search_path=otherSchema,{self.__schema}")
        self.__cursor = self.__connect.cursor()

    def _close_db(self):
        """
        关闭数据库
        """
        self.__cursor.close()
        self.__connect.close()

    def execute_sql(self, sql):
        """
        :param sql: 执行的sql
        :return: 查询结果
        """
        self._connect_db()
        if type(sql) is list:
            for i in sql:
                self.__cursor.execute(i)
        elif type(sql) is str:
            self.__cursor.execute(sql)
        try:
            result = self.__cursor.fetchall()
        except:
            result = None
        self.__connect.commit()
        self._close_db()
        return result

    def get_table_list(self, table_names=None):
        """
        查询数据库里的表
        :param table_names: 表名
        :return:查询结果
        """
        self._connect_db()
        sql = f"SELECT tablename FROM sys_tables WHERE "
        if isinstance(table_names, str):
            sql += f"tablename LIKE '{table_names}'"
        elif isinstance(table_names, list):
            for table in table_names:
                sql += f"tablename LIKE '{table}' or "
        elif table_names is None:
            sql = sql.strip(" WHERE ")
        sql = sql.strip(" or")
        self.__cursor.execute(sql)
        result = self.__cursor.fetchall()
        self._close_db()
        return result


SYS_DATABASE = MysqlManager(db=SYS_DB_NAME, **DATABASE_INFO)
APP_DATABASE = MysqlManager(db=APP_DB_NAME, **DATABASE_INFO)
ACCOUNT_DATABASE = MysqlManager(db=ACCOUNT_DB_NAME, **ACCOUNT_DB_INFO)
# # 信创kingbase
# SYS_DATABASE = PostGreManager(database=SYS_DB_NAME, **DATABASE_INFO)
# APP_DATABASE = PostGreManager(database=APP_DB_NAME, **DATABASE_INFO)
# ACCOUNT_DATABASE = PostGreManager(database=ACCOUNT_DB_NAME, **ACCOUNT_DB_INFO)

if __name__ == '__main__':
    ck = ClickhouseManager(user="admin",
                                  passwd="Y942soX#@3cSWX",
                                  host="cc-2ze34m58grmq0vr5j.ads.rds.aliyuncs.com",
                                  port=3306)
    ck.create_database("test_deep_pipeline", switch_to=True)
    ck.delete_database("test_deep_pipeline")
